User Level Security in MS Access

MS Access provides different ways to protect Access databases. User Level security is one of them. By using this technique, it is possible to protect every object of database on per user basis, means you can set data access permission for a user that is totally different than the permissions set for another. In this way you can protect your database in a managed environment.

The article describes how you can provide user level security to an Access database. It also provides step by step procedure of creating a special type of file that has extension ".MDW" and will be used to store user's database access permissions related information. It also provides tips for opening a database with your own MDW security file declaratively as well as programmatically.

Note: User Level Security can be defined only on the Access database file which has extension .MDB. Access latest file format (.ACCDB) does not support User Level Security, but if you open a secured mdb file into latest version of MS Access (2007, 2010 etc.), all your database file security will be applied and maintained on the database.

Before diving into details, let me discuss something about the default security file named System.mdw to open any unsecured MS Access database. In general every database of MS Access is protected via this file (System.mdw) and when you install Microsoft Office or MS Access on your computer then the file is created on the following location

C:\Users\user name\AppData\Roaming\Microsoft\Access\System.mdw (in Window-7 and Window Vista)

C:\Documents and Settings\\Application Data\Microsoft\Access\System.mdw (in older versions of Windows)

As this file has information related to database protection. Access Database Engine created two groups and one user into this file named

Group Name: Admins and Users

User Name: Admin

Whenever you open any MS Access Database, the database is opened via Admin user credentials of this default security file. By default the Admin user is the user of both of the groups (Admin, User). Thus he has fully access to the database. Default opening behavior of any unprotected Access database is, it's not password protected. You can set password protection to the database by providing password to the default user (Admin) of the default access database protection file (System.mdw). Process of setting user password will be discussed shortly.

As we know System.mdw is the default protection file for Access databases, so if we change anything into the file, it will affect every Access database as we know by default every Access database will you this file for its protection. So it is strongly recommends that, you should not change anything into the default behavior of the file. If you want to protect you Access database by using User Level Security, then you can generate a new MDW file to protect your database. The process of generating a new MDW file and running the database against the newly generated MDW file will be discussed further in the article.

Two important points related to MDW files needs to be remembered

Every user (default or newly created) must be assigned to the Users Group.
At least one user must be assigned to the Admins Group.

To know, currently which MDW file your Access database is using, you can user the following command on Debug window on the Visual Basic Editor.

?SysCmd(13)

When you press enter after typing the above command on the Debug window, absolute path of the currently in use mdw file, reflects on the Debug window.

In this article, I will use MS Access 2010 for creating and maintaining user level security in a file which has extension .MDB. When you open a MDB file into MS Access 2010, you can find out the security options into the File tab -> Info. There is an option 'Manage Users & Permissions' as shown in the following figure.

If your selected file is not of type MDB, then the option will be totally invisible to you, as only MDB files supports this type of security. When you clicks on 'User and Permissions' button, a new context window will appear. There are four options as shown in the above figure. Via using first three among these options, you can protect you database fully.

Let's discuss all options one by one

1). User and Group Permissions

This form can be used to set database objects access permission to the users created via either step (2) or step (3).

When you click on the option, a new window appears as shown in Fig- 1.1

Fig-1.1

Fig-1.2

Now by using this window, you can check or set access permission for users on per object basis. For example-Fig- 1.1 shows Admin user's permissions on database table Authors. There are two lists on this form, list on the left shows all the users or Groups of the database on the basis of selected Users or Groups radio button. List on right show all objects of a specific type base on the selection from the Object type drop-down list. In this case object type is Table. After selecting all this, user can set different kind of permissions on the selected object as shown in Fig 1.1

Note: - You can set permission on objects Database, Table, Query, Form, Report and Macro. You can changes permission for a user only if you have permission to change other user's permissions or you belong to Admins Group.

Fig 1.2 show owner lists of the objects. From here you can change the owner of an object. An owner has full access on object. You can change objects owner details only if you have permissions of doing this.

If you want to see users and their permission on the default System.MDW file then you can check from here and if the database is unprotected (does not use any other MDW file for security) then you can change the default permissions also.

2). User and Group Accounts

New users and groups can be created with the help of this form, when you click on the option; a new window as shown in Fig- 2.1 appears.

Fig-2.1

Fig-2.2

The form shows groups and their underlying user's related information. Fig 2.1 is details of users and groups in the default System.mdw file. As I said earlier, these two groups (Admins and Users) and the user (Admin) are created by default in this mdw file and all the Access databases are opened by default with Admin User's credentials. You can add new yours in the list by clicking on the New... button on the form. When you click on it, a new popup window appears as shown in Fig- 2.2. From here, you can add new users to the list by providing user name and personal ID. Personal ID uniquely identifies users. When you click on the OK button, the user is created on reflected in the dropdown list on the form. Now from here, you can assign groups to the newly created user.

New Groups can be created from the Groups tab on the form. To create a new group, click on the Groups tab. The form looks something like shown in Fig- 2.3. Now when you click on the New... button on this form, a new popup window as shown in Fig- 2.4 appears. By providing Group Name and personal ID and clicking on OK button, you can create new group in the list. Here Personal ID uniquely identifies Groups.

From the third tab on the form, you can set or change your password. When you click on the Change Logon Password tab, the form looks something like shown in Fig- 2.5. Here if you are assigning password for the first time, then leave the Old Password textbox blank. Write down your password in the New Password textbox and confirm your new password by providing same value in the Verify textbox. After providing all this, when you click on the OK button, password has been assign to you. Now the user can logon to the underlying database by providing his user name and password information.

Fig-2.3

Fig-2.4

As we know by default all access database files are opened by using Admin user's credentials defined in the System.mdw file. If admin user did not set his password, no login window will appears, does not matter other users defined in the file set their password information.

Fig-2.5

Now to open a database with logon window you have two options-

A. Set password for the Admin user defined in the default System.mdw file- The option is never recommended because when you set password to the Admin user in this file. All access databases will open with the logon window, does not matter whether it's a new database or you are opening any existing one. Remember any changes in the default System.mdw file will effects on all access databases on the machine.

B.Use different MDW file for the Database- The option is well suited to protect a database. You can generate your own mdw security file to protect your access database. You can configured you database in such a way that, whenever you opens it by double clicking, it will open itself by using mdw file defined by you not the default. The process of configuring database for this will be discussed further section on the article.

3). User-Level Security Wizard

From here you can created a new MDW file to protect your Access Database. The wizard will guide you for creating a new MDW security file and for creating new users, assign groups to users, assigning permissions to individual user or group in the internal step of the wizard. When you click on the option, first window of the wizard appears as shown in Fig-3.1

Fig-3.1

Now to open a database with logon window you have two options-

On the window option Create a new workgroup information file is selected. This shows that you are going to create a new mdw security file for your opened access database. Just click Next from here.

Fig-3.2

Next window allows you to define location of the new MDW window that will be created after completing the wizard. You can define the location (including file name) by clicking on the Browse button on the form-A auto generated workgroup ID will reflect on the WID textbox. You need not to change this auto generated value. Optionally you can also define your name as sell as your company name here.

There are two radio buttons on the form. First option allows you to make this MDW file your default MDW file for all access databases in place of System.mdw file. Second option creates a separate MDW security file and you can open a database with this MDW file by using shortcut tips (will be discussed later). Make sure you select second option as we do not want to make this MDW as default security file for all access databases. Now clicks on the Next button.

Next window allows you to select database objects to which you want to put security permissions as shown in Fig-3.3. By default all database objects are selected. From here you can deselect the object by clicking on the check box in front on object name. We are going to set permissions on all objects. So leave all the objects selected and click on the Next button on the form.

Fig-3.3

Next window shows in build groups those have different permissions on the database. You can see the description of their permissions by selecting the group and reading the permission description on the area of the form that have heading Group Permissions.

Fig-3.4

Here GroupID uniquely identifies the group, it contains auto generated value and maintain internal by access database engine. So do not change the GroupID value here. After reading all groups permissions description, you will have an idea of what area of database, users of a group can access. You can optionally select any group from here by checking the checkbox in front of the group name. After doing your selection, click on the Next button.

Fig-3.5

As we know any MDW file must contains two groups (Admins and Users). Optionally you can add new groups to the file either by the previous step or by 'User and Group Account' discussed previously. By default Admins group has full access to the database. You can set the permission of Users Group from this window in this step of the wizard. As shown in Fig- 3.5, I gave open database permission to the Users group. So any user who belongs to this group can only open the database. Nothing else he can do. So from here, you can set permissions on each object types of the database to all users belong to the Users group. After setting these permissions click on the Next button for further steps.

Next window allows you to create new user to the security file. By default a user based on the window user will be in the list. You can create your own users by specifying user's name and password as shown in Fig- 3.6. Here PID uniquely identifies each user in the list, by default it show an auto generated value for the PID, you can change it but it must be unique. As it is internally maintain by Access database engine, so you do not need to change this value. Click on the 'Add This User in the List' button adds the user. From here you can also delete a user by selecting the user from the list and clicking on the 'Delete User from the List' button on the form.

After defining your entire user, click on the next button for further step of the MDW file creation wizard. In the next step you can assign your users to the groups. Here I have two options. Assigning users to groups or assigning groups to user. Select what you want, both put the same effect. As shown in Fig- 3.7, I assign Admins group to the user named ABC (I created on the previous step).

Fig-3.6

Note: - There must be one User that belongs to the Admins Group. The group is created internally by ADE and has full access to the database. Persons like DBA, System Owner, come under this Group.

Fig-3.7

After assigning user to groups or groups to users, click on the next step of the wizard. Last window of the wizard allow you to define location at which the backup of your database can be placed. Before applying security on you database, the wizard takes the backup of it and put it on the location selected by user from here as shown in Fig- 3.8.

Fig-3.8

After selecting the database backup location, click on the Finish button to complete the wizard. When you click on the button, a report which contains information related to users, their passwords, their assigning group appears as shown in Fig- 3.9. As this is confidential information save it to the secured location. You will need this file whenever in the future you need to re-create your workgroup file.

Fig-3.9

Now your database security file is created on the location defined by you in second step of the wizard. Unfortunately by default, your database is not opened with this security file. When you double click on the database, it will open by using System.mdw security file. You have to do some extra work for your desired expectations.

I will tell you how you can set the default security file to a database programmatically but before that I am going to discuss something that generally confuse persons who secure their access database in this way.

As we know if we do not apply trick to open an access database with the desired MDW file. Database is opened by Admin user in the default System.mdw file. When you create your own security file, the wizard (discussed previously) internally creates a user named 'Admin' and assign Users group to him. As in the fifth step of the wizard, we assign permission to the Users groups. All the permission applied on that time for Users group, will apply for this Admin user as he is a member of this group.

Now if you open you database by double clicking on it, it will be opened by the Admin user and nothing else he can do because we only set database opening permissions to the user's group. So in this way you can protect you database, when you do not want to give you MDW file along with the database to someone.

When you open a secured database by double clicking on it, Admin user of system.mdw file will be used to open the database in this way. Now if you check this user's permissions (checking permissions process discussed earlier), you will find that the user is not an owner of database objects as shown in Fig- 4. So he can do nothing with the database except opening it as we give database opening permission to it.

Fig-4

Let's discuss different ways to open a access database with the security file we created in the previous step.

1) Opening a database with defined security MDW file using command line tool

To open a access database with a particular mdw file, write the following lines in the command line tool of the window.

c:\>"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" /wrkgrp C:\Users\Administrator\Desktop\Test\Security.mdw"

Here in the line, first string is the location of the MSACCESS.EXE file, /wrkgrp is Access identifier to represent workgroup, second string is the location of you MDW file (in this example its Security.mdw).

When you press enter by typing the information in this way, you database will open with the defined MDW file and a login window will appears as shown in the following Figure

Opening a database with defined security MDW file using command line tool

Fig-5

Here MaccUser is the default admin user of my security file. User name may be different on the basis of admin user defined in your security file.

2) Opening a database with defined security MDW file using shortcut

To open a access database with a particular mdw file, write the following lines in the command line tool of the window.

C:\>"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" /wrkgrp C:\Users\Administrator\Desktop\Test\Security.mdw"

When you press enter by typing the information in this way, you database will open with the defined MDW file and a login window will appears as shown in the following Figure

Fig-5

Here MaccUser is the default admin user of my security file. User name may be different on the basis of admin user defined in your security file.

You can also using shortcut method to open a database with the mdw file defined in the shortcut. To do so, first of all create a shortcut on the desktop, by right clicking, and selecting New and then Shortcut from the context menu.

Fig-6

Fig-7

Now write down the following command in the text box in front of the brows button on the form as shown in Fig- 7.

"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" /wrkgrp C:\Users\Administrator\Desktop\Test\Security.mdw"

Click next and give name to the shortcut as shown in Fig- 8. Click on the Finish button. Your shortcut is created on the desktop.

Fig-8

Now if you drag any access database over this shortcut. Your database will be opened by using the security MDW file defined in the shortcut.

If you want that you need not to drag you database over the shortcut you defined here, whenever you double click on the shortcut, you database opens with the security file defined in the shortcut. To get this you need to mention the database file in the shortcut. So shortcut string should be something like

"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" "C:\Users\Administrator\Desktop\MACC Test\Access.mdb" /wrkgrp "C:\Users\Administrator\Desktop\MACC Test\Security.mdw"

Here the second string tells the location of the database. Now if you simply double click on the shortcut, the defined database will be opened with the defined security file.

3) Opening a database with defined security MDW file directly

Its look fine and works well, but what happen if a user complaints about these steps (mentioned above) that I do not want to create any shortcut. We know if user directly double clicks on the database file, database will be opened with system.mdw file, not with your desired security file. That's not what the user wants by applying minimum efforts.

You can get the desired functionality by a simple trick. To get this you have to create a procedure that will open the database with the desired MDW security file and will be used in the AutoExec macro. As we know AutoExec macro is the default macro that runs whenever an access database is opened.

My trick of getting desired condition is that, first of all I created a table named 'Secured' into the database with single column named 'IsDatabasesecured' of type Yes/No

Now I created in procedure name Set Start up MDW File which gets the value from the table and opened the database again on the basis of the column value from the table. The whole procedure is defined as follows.

Public Function SetStartupMDWFile()
Dim securedfileattached As Boolean
securedfileattached = DLookup("IsSecured", "Secured")
If Not (securedfileattached) Then
Dim strpath As String
strpath = """C:\Program Files\Microsoft Office\Office14\
MSACCESS.EXE"" """ CurrentProject.Path & "\MACC.mdb""
/wrkgrp """ & CurrentProject.Path & "\Security.mdw""
/user MaccUser /pwd Macc"
CurrentDb.Execute "Update Secured Set IsSecured = True"
Shell strpath, vbNormalFocus
Application.DoCmd.Quit acQuitSaveAll
End If
End Function

Now I called this procedure on the AutoExec macro, so whenever the database is opened directly, AutoExec macro runs the procedure and the procedure opens the database once again with database administrator credentials defined in the mentioned security MDW file.

Note that at the time of closing the database, you must set the column value of the table to False so that next time the database is opened, the procedure runs correctly.

Other than this, while creating my MDW security file using wizard, I gave database opening permissions to Users Groups and assign full permission to Admin user on table 'Secured' by using process defined in Users and Groups Permission Section.

Conclusion: At first site, Access User Level Security look little bit confusing, but if you grasp the basics of how to use MDW files, you can well protect your access database. You need to do all actions very carefully. Any loop hole in your security file can allow fraudsters to access confidential part of your database.

DISCLAIMER

It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.